SQL Server must generate audit records for the DoD-selected list of auditable events.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-41035	SQL2-00-011400	SV-53410r4_rule		Medium

Description
Audit records can be generated from various components within the information system, such as network interfaces, hard disks, modems, etc. From an application perspective, certain specific application functionalities may be audited, as well. The list of audited events is the set of events for which audits are to be generated. This set of events is typically a subset of the list of all events for which the system is capable of generating audit records (i.e., auditable events, time stamps, source and destination addresses, user/process identifiers, event descriptions, success/fail indications, file names involved, and access control or flow control rules invoked). Organizations may define the organizational personnel accountable for determining which application components shall provide auditable events. Auditing provides accountability for changes made to the SQL Server configuration or its objects and data. It provides a means to discover suspicious activity and unauthorized changes. Without auditing, a compromise may go undetected and without a means to determine accountability.

Description

Audit records can be generated from various components within the information system, such as network interfaces, hard disks, modems, etc. From an application perspective, certain specific application functionalities may be audited, as well. The list of audited events is the set of events for which audits are to be generated. This set of events is typically a subset of the list of all events for which the system is capable of generating audit records (i.e., auditable events, time stamps, source and destination addresses, user/process identifiers, event descriptions, success/fail indications, file names involved, and access control or flow control rules invoked). Organizations may define the organizational personnel accountable for determining which application components shall provide auditable events. Auditing provides accountability for changes made to the SQL Server configuration or its objects and data. It provides a means to discover suspicious activity and unauthorized changes. Without auditing, a compromise may go undetected and without a means to determine accountability.

STIG	Date
Microsoft SQL Server 2012 Database Instance Security Technical Implementation Guide	2017-07-13

Details

Check Text ( C-47652r4_chk )
Check to see that all required events are being audited. From the query prompt: SELECT DISTINCT traceid FROM sys.fn_trace_getinfo(0); All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding. Determine the trace(s) being used for the auditing requirement. In the following, replace # with a trace ID being used for the auditing requirements. From the query prompt: SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#); The following required event IDs should be listed: 14, 15, 18, 20, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 115, 116, 117, 118, 128, 129, 130, 131, 132, 133, 134, 135, 152, 153, 170, 171, 172, 173, 175, 176, 177, 178. If any of the audit event IDs required above is not listed, this is a finding. Notes: 1. It is acceptable to have the required event IDs spread across multiple traces, provided all of the traces are always active, and the event IDs are grouped in a logical manner. 2. It is acceptable, from an auditing point of view, to include the same event IDs in multiple traces. However, the effect of this redundancy on performance, storage, and the consolidation of audit logs into a central repository, should be taken into account. 3. It is acceptable to trace additional event IDs. This is the minimum list. 4. Once this check is satisfied, the DBA may find it useful to disable or modify the default trace that is set up by the SQL Server installation process. (Note that the Fix does NOT include code to do this.) Use the following query to obtain a list of all event IDs, and their meaning: SELECT * FROM sys.trace_events; 5. Because this check procedure is designed to address multiple requirements/vulnerabilities, it may appear to exceed the needs of some individual requirements. However, it does represent the aggregate of all such requirements. 6. Microsoft has flagged the trace techniques and tools used in this Check and Fix as deprecated. They will be removed at some point after SQL Server 2014. The replacement feature is Extended Events. If Extended Events are in use, and cover all the required audit events listed above, this is not a finding.

Check Text ( C-47652r4_chk )

Check to see that all required events are being audited.
From the query prompt:
SELECT DISTINCT traceid FROM sys.fn_trace_getinfo(0);
All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding.

Determine the trace(s) being used for the auditing requirement.
In the following, replace # with a trace ID being used for the auditing requirements.
From the query prompt:
SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#);
The following required event IDs should be listed:
14, 15, 18, 20,
102, 103, 104, 105, 106, 107, 108, 109, 110,
111, 112, 113, 115, 116, 117, 118,
128, 129, 130,
131, 132, 133, 134, 135,
152, 153,
170, 171, 172, 173, 175, 176, 177, 178.
If any of the audit event IDs required above is not listed, this is a finding.

Notes:
1. It is acceptable to have the required event IDs spread across multiple traces, provided all of the traces are always active, and the event IDs are grouped in a logical manner.
2. It is acceptable, from an auditing point of view, to include the same event IDs in multiple traces. However, the effect of this redundancy on performance, storage, and the consolidation of audit logs into a central repository, should be taken into account.
3. It is acceptable to trace additional event IDs. This is the minimum list.
4. Once this check is satisfied, the DBA may find it useful to disable or modify the default trace that is set up by the SQL Server installation process. (Note that the Fix does NOT include code to do this.)
Use the following query to obtain a list of all event IDs, and their meaning:
SELECT * FROM sys.trace_events;
5. Because this check procedure is designed to address multiple requirements/vulnerabilities, it may appear to exceed the needs of some individual requirements. However, it does represent the aggregate of all such requirements.
6. Microsoft has flagged the trace techniques and tools used in this Check and Fix as deprecated. They will be removed at some point after SQL Server 2014. The replacement feature is Extended Events. If Extended Events are in use, and cover all the required audit events listed above, this is not a finding.

Fix Text (F-46334r5_fix)
Create a trace that meets all auditing requirements. The script provided in the supplemental file, Trace.sql, can be used to do this; edit it as necessary to capture any additional, locally defined events.